package devbureau.fstore.common.db;


import devbureau.fstore.common.DeliveryInfo;
import devbureau.fstore.common.Item;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;


public class LoadDeliveryDBTask<T extends DeliveryInfo> extends DBTask{

	List<Item> items = null;
    T delivery = null;
    private Integer deliveryId = null;

   	private static Logger logger = Logger.getLogger(LoadDeliveryDBTask.class);

	public static final String SQL_NEXT_ITEM = "values nextval for STORE.ITEM_SEQ";
	public static final String SQL_INSERT_ITEM = "insert into STORE.ITEM(ID, SUPPLIER_LABEL, NAME, LENGTH, INCOMING_DATE, PRICE, PRICE_RUR, BALANCE, PLANTATION) values (?,?,?,?,?,?,?,?,?)";
    public static final String SQL_UPDATE_ITEM_ADD_QTTY = "update STORE.ITEM set BALANCE = (BALANCE + ?), MODIFIED = CURRENT_TIMESTAMP where ID = ?";
    public static final String SQL_INSERT_DELIVERY_LINE = "insert into STORE.DELIVERY_LINE(DELIVERY_ID, ITEM_ID, QUANTITY) values (?,?,?)";
    public static final String SQL_UPDATE_DELIVERY_LINE_ADD_QTTY = "update STORE.DELIVERY_LINE set QUANTITY = (QUANTITY + ?) where DELIVERY_ID = ? and ITEM_ID = ?";
    public static final String SQL_CHECK_ITEM = "select ID from STORE.V_ITEM where SUPPLIER_LABEL = ? and PLANTATION = ? and  NAME = ?  and  INCOMING_DATE = ? and  PRICE = ? and  CURRENCY = ? and  RATE = ?";
    public static final String SQL_CHECK_DELIVERY_LINE = "select count(*) from STORE.DELIVERY_LINE where DELIVERY_ID = ? and ITEM_ID = ?";
	public static final String SQL_NEXT_DELIVERY = "values nextval for STORE.DELIVERY_SEQ";
	public static final String SQL_INSERT_DELIVERY = "insert into STORE.DELIVERY(ID,FILE_DATA,FILE_NAME,RATE, LOGIN, CURRENCY) values (?,?,?,?,?,?)";
	
	public LoadDeliveryDBTask(Connection connection,List<Item> items,T delivery) {
        super(connection);
		this.delivery = delivery;
        this.items = items;
	}

    private Integer checkDeliveryLine(String supplierLabel, String plantation, String name, Integer length, java.util.Date incomingDate, Double price, String currency, Double rate) throws Exception{
        Integer itemId = null;
        logger.debug("#checkDeliveryLine enter");

        PreparedStatement ps =null;
        ResultSet rs  = null;
        String sql = SQL_CHECK_ITEM;

        if(length != null){
            sql += " and LENGTH = ?";
        }else{
            sql += " and LENGTH is NULL";
        }

        ps = connection.prepareStatement(sql);
        ps.setString(1, supplierLabel);
        ps.setString(2,plantation);
        ps.setString(3, name);
        ps.setDate(4, new java.sql.Date(incomingDate.getTime()));
        ps.setDouble(5, price);
        ps.setString(6, currency);
        ps.setDouble(7, rate);

        if(length != null){
            ps.setInt(8, length);
        }
        
        rs = ps.executeQuery();
        while(rs.next()){
            if(itemId == null) {
                itemId = rs.getInt("ID");
            }else{
                throw new Exception("Оишбка загрузки поставки. Позиция " + name + " " + plantation + "уже существует.");
            }
        }

        logger.info("#checkDeliveryLine itemId = " + itemId);
        logger.debug("#checkDeliveryLine exit");
        return itemId;
    }

	public void run() throws Exception {
        logger.debug("#run enter");
        PreparedStatement ps =null;
        PreparedStatement psNextId =null;
        ResultSet rsNextId  = null;

		try{
			connection.setAutoCommit(false);

            deliveryId = addDelivery(delivery.getLogin(), delivery.getContent(), delivery.getFileName(), delivery.getRate(), delivery.getCurrency());
            logger.debug("#run deliveryId = " + deliveryId);

            Iterator<Item> iterator = items.iterator();
            while(iterator.hasNext()){
                Item item = iterator.next();
                logger.trace("#run item = " + item);

                Integer itemId = checkDeliveryLine(item.getSupplierLabel(),item.getPlantation(), item.getName(), item.getLength(), item.getDeliveryDate(), item.getPrice(), delivery.getCurrency(), delivery.getRate());

                if(itemId == null) {
                    addItem(item);
                }else{
                    updateItem(item,itemId);
                }
            }
			connection.commit();
		}catch(Exception ex){
            logger.error("#run Transaction roll back ", ex);
			connection.rollback();

            throw new Exception("#run Не удалось загрузить в БД информацию об обновлении склада. Ошибка : " + ex.getMessage());
		}finally{
			connection.setAutoCommit(true);
		}
        logger.debug("#run exit");
	}


	protected Integer addDelivery(String login, byte[] fileData, String fileName, Double rate, String currency) throws Exception {
        logger.debug("#addDelivery enter fileData["+fileData+"] fileName["+fileName+"] rate["+rate+"] currency[" + currency + "]");

        PreparedStatement ps =null;
        PreparedStatement psNextId =null;
        ResultSet rsNextId  = null;

        psNextId = connection.prepareStatement(SQL_NEXT_DELIVERY);
        rsNextId  = psNextId.executeQuery();
        if(rsNextId.next()){
            deliveryId = rsNextId.getInt(1);
        }

        rsNextId.close();
        psNextId.close();
		ps = connection.prepareStatement(SQL_INSERT_DELIVERY);
		ps.setInt(1,deliveryId);
		ps.setBytes(2,fileData);
		ps.setString(3,fileName);
        ps.setDouble(4,rate);
        ps.setString(5,login);
        ps.setString(6, currency);

    	ps.executeUpdate();
        ps.close();

        logger.debug("#addDelivery deliveryId = " + deliveryId);
        logger.debug("#addDelivery exit");
        return deliveryId;
	}


    void addItem(Item item) throws Exception{

        PreparedStatement ps =null;
        PreparedStatement psNextId =null;
        ResultSet rsNextId  = null;

        psNextId = connection.prepareStatement(SQL_NEXT_ITEM);
        rsNextId  = psNextId.executeQuery();

        Integer itemNextId = null;
        if(rsNextId.next()){
            itemNextId = rsNextId.getInt(1);
        }
        rsNextId.close();
        psNextId.close();

        logger.debug("#run addItem item(ID["+itemNextId+"],SUPPLIER_LABEL["+item.getSupplierLabel()+"],NAME["+item.getName()+"]," +
                                 "LENGTH["+item.getLength()+"],INCOMING_DATE["+item.getDeliveryDate()+"],PRICE["+item.getPrice()+"],PRICE_RUR["+item.getPrice()*delivery.getRate()+"],BALANCE["+item.getQuantity()+"])");
        ps = connection.prepareStatement(SQL_INSERT_ITEM);

        ps.setInt(1, itemNextId);
        ps.setString(2,item.getSupplierLabel());
        ps.setString(3,item.getName());

        if(item.getLength() != null) {
            ps.setInt(4,item.getLength());
        }else{
            ps.setNull(4,Types.INTEGER);
        }

        if(item.getDeliveryDate() != null) {
            ps.setDate(5,new java.sql.Date(item.getDeliveryDate().getTime()));
        }else{
            ps.setNull(5,Types.DATE);
        }

        ps.setDouble(6,item.getPrice());
        ps.setDouble(7,item.getPrice()*delivery.getRate());

        ps.setInt(8,item.getQuantity());
        
        ps.setString(9, item.getPlantation());

        ps.executeUpdate();
        ps.close();

        logger.debug("#run itemId = " + itemNextId + " create OK");
        ps = connection.prepareStatement(SQL_INSERT_DELIVERY_LINE);

        ps.setInt(1, deliveryId);
        ps.setInt(2,itemNextId);
        ps.setInt(3,item.getQuantity());
        ps.executeUpdate();
        ps.close();

        logger.debug("#run deliveryLine create OK");
    }

    public void updateItem(Item item, Integer itemId) throws Exception{
        PreparedStatement ps =null;
        ps = connection.prepareStatement(SQL_UPDATE_ITEM_ADD_QTTY);

        ps.setInt(1, item.getQuantity());
        ps.setInt(2, itemId);

        ps.executeUpdate();
        ps.close();


        ps = connection.prepareStatement(SQL_CHECK_DELIVERY_LINE);

        ps.setInt(1, deliveryId);
        ps.setInt(2,itemId);

        long count = 0;
        ResultSet rs =  ps.executeQuery();
        if(rs.next()) {
            count = rs.getLong(1);
        }
        rs.close();
        ps.close();


        if(count > 0) {
            ps = connection.prepareStatement(SQL_UPDATE_DELIVERY_LINE_ADD_QTTY);
            ps.setInt(1,item.getQuantity());
            ps.setInt(2, deliveryId);
            ps.setInt(3,itemId);
        }else{
            ps = connection.prepareStatement(SQL_INSERT_DELIVERY_LINE);
            ps.setInt(1, deliveryId);
            ps.setInt(2,itemId);
            ps.setInt(3,item.getQuantity());
        }
        ps.executeUpdate();
        ps.close();

        logger.debug("#run deliveryLine create OK");

    }

    public Integer getDeliveryId() {
        return deliveryId;
    }
}
